



SELECT distinct zppcg151, LIB_RUBRIQUE, 'P.0.'||substr(zppcg151,1,3)
from FI_GEN_PLAN_COMPTABLE,fi_ana_rubrique
 where FI_GEN_PLAN_COMPTABLE.CODE_PARAM_CODE='CSCIV' 
and  FI_ana_rubrique.CODE_PARAM_CODE='CSCIV' 
and zppcg151 is not null
and fk_type_compte is not null
and zppcg151 = uk_code
order by 1;

SELECT 'Activité', FI_ANA_AXE_ANALYTIQUE4.UK_CODE, FI_ANA_AXE_ANALYTIQUE4.LIB_AXE  FROM FI_ANA_AXE_ANALYTIQUE4
 where FI_ANA_AXE_ANALYTIQUE4.CODE_PARAM_CODE='CMS';
 
 SELECT distinct zppcg151, LIB_RUBRIQUE, 'P.0.'||substr(zppcg151,1,3), uk_numero_compte
from FI_GEN_PLAN_COMPTABLE,fi_ana_rubrique
 where FI_GEN_PLAN_COMPTABLE.CODE_PARAM_CODE='CSCIV' 
and  FI_ana_rubrique.CODE_PARAM_CODE='CSCIV' 
and zppcg151 is not null
and fk_type_compte is not null
and zppcg151 = uk_code 
and substr(uk_numero_compte,1,1) between 6 and 7;

SELECT 'Activité', FI_ANA_AXE_ANALYTIQUE4.UK_CODE, FI_ANA_AXE_ANALYTIQUE4.LIB_AXE  FROM FI_ANA_AXE_ANALYTIQUE4
 where FI_ANA_AXE_ANALYTIQUE4.CODE_PARAM_CODE='CMS';

select * from FI_GEN_PLAN_COMPTABLE;

select *
from fi_ana_rubrique
where uk_code='M6408'
and code_param_code='CSCIV'



select A.FK_SOCIETE, FK_ETABLISSEMENT,
zppcg151, A.FK_COMPTE, A.FK_AXE0 , A.FK_AXE1, A.FK_AXE3 , A.FK_AXE4 ,  A.FK_AXE5 ,  A.FK_AXE6 ,  A.FK_AXE7 , 
 A.FK_AXE8 ,A.FK_AXE9  , A.FK_RUBRIQUE , CASE when SENS = '1' then sum(MONTANT_ANA_MONNAIE1*SENS) else 0 end as SOLDE_DEBIT_MONNAIE1, 
 CASE when SENS = '-1' then sum(MONTANT_ANA_MONNAIE1*(-SENS)) else 0 end as SOLDE_CREDIT_MONNAIE1
 FROM FI_GEN_ECRITURE_ANALYTIQUE A, FI_GEN_PLAN_COMPTABLE B 
WHERE A.FK_SOCIETE = 'SCI'
  and (fK_classe_ecriture is null or fk_classe_ecriture<'IFRS')      
and FK_CENTRALISATEUR = UK_NUMERO_COMPTE
and CODE_PARAM_CODE = 'CSCIV'
and zppcg151 is not null and zppcg151 <> 'INV'
group by A.FK_SOCIETE , A.FK_eTABLISSEMENT, zppcg151, A.FK_COMPTE, A.FK_AXE0 , 
 A.FK_AXE1 , A.FK_AXE3 , A.FK_AXE4 ,  A.FK_AXE5 ,  A.FK_AXE6 ,  A.FK_AXE7 ,  A.FK_AXE8, A.FK_AXE9 , A.FK_RUBRIQUE, SENS; 
 
 
select A.FK_SOCIETE, FK_ETABLISSEMENT,
zppcg151,  null as FK_COMPTE,  A.FK_AXE0 , A.FK_AXE1 , null as FK_AXE2, A.FK_AXE3 ,  A.FK_AXE5 ,  A.FK_AXE6 ,  A.FK_AXE7 , 
 A.FK_AXE8 ,A.FK_AXE9  , A.FK_RUBRIQUE , CASE when SENS = '1' then sum(MONTANT_ANA_MONNAIE1*SENS) else 0 end as SOLDE_DEBIT_MONNAIE1, 
 CASE when SENS = '-1' then sum(MONTANT_ANA_MONNAIE1*(-SENS)) else 0 end as SOLDE_CREDIT_MONNAIE1 
 FROM FI_GEN_ECRITURE_ANALYTIQUE A, FI_GEN_PLAN_COMPTABLE B 
WHERE A.FK_SOCIETE = 'SCI'
and (fK_classe_ecriture is null or fk_classe_ecriture<'IFRS')      
and FK_CENTRALISATEUR = UK_NUMERO_COMPTE
and CODE_PARAM_CODE = 'CSCIV'
and zppcg151 is not null and zppcg151 <> 'INV' 
group by A.FK_SOCIETE , A.FK_eTABLISSEMENT, zppcg151, A.FK_AXE0 , 
 A.FK_AXE1, A.FK_AXE3 ,  A.FK_AXE5 ,  A.FK_AXE6 ,  A.FK_AXE7 ,  A.FK_AXE8, A.FK_AXE9 , A.FK_RUBRIQUE , SENS;  
 
 
select A.FK_SOCIETE, FK_ETABLISSEMENT,
zppcg151, A.FK_AXE0 , A.FK_AXE1 , A.FK_AXE3 , A.FK_AXE4 ,  A.FK_AXE5 ,  A.FK_AXE6 ,  A.FK_AXE7 , 
 A.FK_AXE8 ,A.FK_AXE9  , A.FK_RUBRIQUE , CASE when SENS = '1' then sum(MONTANT_ANA_MONNAIE1*SENS) else 0 end as SOLDE_DEBIT_MONNAIE1, 
 CASE when SENS = '-1' then sum(MONTANT_ANA_MONNAIE1*(-SENS)) else 0 end as SOLDE_CREDIT_MONNAIE1, '20140901', '20140930' 
 FROM FI_GEN_ECRITURE_ANALYTIQUE A, FI_GEN_PLAN_COMPTABLE B 
WHERE A.FK_SOCIETE = 'SCI'
  and DATE_COMPTABLE between to_date('20140901','yyyymmdd') and to_date('20140930','yyyymmdd')
  and (fK_classe_ecriture is null or fk_classe_ecriture<'IFRS')      
and FK_CENTRALISATEUR = UK_NUMERO_COMPTE
and CODE_PARAM_CODE = 'CSCIV'
and zppcg151 is not null and zppcg151 <> 'INV' and zppcg151 = 'M6202'
group by A.FK_SOCIETE , A.FK_eTABLISSEMENT, zppcg151, A.FK_AXE0 , 
 A.FK_AXE1 , A.FK_AXE3 , A.FK_AXE4 ,  A.FK_AXE5 ,  A.FK_AXE6 ,  A.FK_AXE7 ,  A.FK_AXE8, A.FK_AXE9 , A.FK_RUBRIQUE ,  '20140901', '20140930' , SENS; 
 
 
select A.FK_SOCIETE,FK_ETABLISSEMENT,zppcg151,A.FK_COMPTE,
A.FK_AXE0,A.FK_AXE1,A.FK_AXE2,A.FK_AXE3,A.FK_AXE4,A.FK_AXE5,A.FK_AXE6,A.FK_AXE7,A.FK_AXE8,A.FK_AXE9, A.FK_RUBRIQUE, 
CASE when SENS = '1' then sum(MONTANT_ANA_MONNAIE1*SENS) else 0 end as SOLDE_DEBIT_MONNAIE1, 
CASE when SENS = '-1' then sum(MONTANT_ANA_MONNAIE1*(-SENS)) else 0 end as SOLDE_CREDIT_MONNAIE1
FROM FI_GEN_ECRITURE_ANALYTIQUE A, FI_GEN_PLAN_COMPTABLE B 
WHERE A.FK_SOCIETE = 'SCI'
and (fK_classe_ecriture is null or fk_classe_ecriture<'IFRS')      
and FK_CENTRALISATEUR = UK_NUMERO_COMPTE
and CODE_PARAM_CODE = 'CSCIV'
and zppcg151 is not null and zppcg151 <> 'INV'
group by A.FK_SOCIETE,A.FK_eTABLISSEMENT,zppcg151,A.FK_COMPTE,A.FK_AXE0,A.FK_AXE1,A.FK_AXE2,A.FK_AXE3 ,A.FK_AXE4,A.FK_AXE5,A.FK_AXE6,A.FK_AXE7,A.FK_AXE8,A.FK_AXE9,A.FK_RUBRIQUE;

select A.FK_SOCIETE, FK_ETABLISSEMENT,zppcg151, A.FK_COMPTE , A.FK_AXE0 , A.FK_AXE1 , A.FK_AXE2 , A.FK_AXE3 , A.FK_AXE4 ,  A.FK_AXE5 ,  A.FK_AXE6 ,  A.FK_AXE7 ,  A.FK_AXE8 ,A.FK_AXE9  , A.FK_RUBRIQUE , CASE when SENS = '1' then sum(MONTANT_ANA_MONNAIE1*SENS) else 0 end as SOLDE_DEBIT_MONNAIE1, 
 CASE when SENS = '-1' then sum(MONTANT_ANA_MONNAIE1*(-SENS)) else 0 end as SOLDE_CREDIT_MONNAIE1, '20141001', '20141031' 
 FROM FI_GEN_ECRITURE_ANALYTIQUE A, FI_GEN_PLAN_COMPTABLE B 
 WHERE A.FK_SOCIETE = 'SCI'
  and DATE_COMPTABLE between to_date('20140101','yyyymmdd') and to_date('20140930','yyyymmdd')
  and (fK_classe_ecriture is null or fk_classe_ecriture<'IFRS')      
and FK_CENTRALISATEUR = UK_NUMERO_COMPTE
and CODE_PARAM_CODE = 'CSCIV'
and zppcg151 is not null and zppcg151 <> 'INV'
group by A.FK_SOCIETE , A.FK_eTABLISSEMENT, zppcg151, A.FK_COMPTE , A.FK_AXE0 ,  A.FK_AXE1 , A.FK_AXE2 , A.FK_AXE3 , A.FK_AXE4 ,  A.FK_AXE5 ,  A.FK_AXE6 ,  A.FK_AXE7 ,  A.FK_AXE8, A.FK_AXE9 , A.FK_RUBRIQUE , SENS;